package com.minisoft.exhibition;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.minisoft.enums.AgentTypeEnum;
import org.junit.Test;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author JueSu
 * @description: TODO
 * @date 2023/6/25 16:17
 */
public class ExportData {

    public static String DB_CENTER = "center-pro";
    public static String DB_YDAC = "ydac-pro";

    @Test
    public void testExport() throws SQLException {
        ExcelWriter writer = ExcelUtil.getWriter("C:\\Users\\31594\\Downloads\\异地门岗系统确访详表1.xlsx");

        List<Entity> exhList = Db.use(DB_YDAC).query("select ei.name ,c.name customer_name,if(c.gender = 0,'男','女') gender,ifnull(c.full_mobile ,c.mobile) mobile,c.channel_name ,c.report_id , l.report_id appoint_report_id,c.create_time first_visist_time,c.sale_main_name \n" +
                "from exh_customer c " +
                "left join exh_info ei on ei.uuid = c.exh_id " +
                "left join exh_appoint_log l on l.customer_id = c.id");
        for (int i = 0; i < exhList.size(); i++) {
            Entity entity = exhList.get(i);
            int rowIdx = i + 1,colIdx = 0;
            System.out.println(entity);

            writer.writeCellValue(colIdx++, rowIdx, rowIdx)
                    .writeCellValue(colIdx++, rowIdx, entity.getStr("name"))
                    .writeCellValue(colIdx++, rowIdx, entity.getStr("customer_name"))
                    .writeCellValue(colIdx++, rowIdx, entity.getStr("gender"))
                    .writeCellValue(colIdx++, rowIdx, entity.getStr("mobile"))
                    .writeCellValue(colIdx++, rowIdx, entity.getStr("channel_name"));
            Object reportId = entity.get("report_id");
            if (reportId != null) {
                Entity report = Db.use(DB_CENTER).queryOne("select cr.agent_type_no ,cr.sale_main_name ,cr.report_date ,st.sale_team_name ,cr.report_status ,cr.deal_date ,co.signing_date ,co.house_name ,co.order_total_price  from customer_report cr \n" +
                        "join sale_team st on st.sale_team_id = cr.sale_team_id \n" +
                        "left join customer_order_rel cor on cor.report_id = cr.report_id and cor.deleted = 0\n" +
                        "left join customer_order co on co.order_id = cor.ticket_number and co.order_type = '10'\n" +
                        "where cr.report_id = ? and (co.order_state in ('10','20') or co.order_state is null)", entity.get("report_id"));
                if (report != null) {
                    writer.writeCellValue(colIdx++, rowIdx, report.getStr("sale_team_name"))
                            .writeCellValue(colIdx++, rowIdx, AgentTypeEnum.getMsgByCode(report.getStr("agent_type_no")))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("report_date"));
                }
            }
            colIdx = 9;
            writer.writeCellValue(colIdx++, rowIdx, entity.getStr("first_visist_time"));
            writer.writeCellValue(colIdx++, rowIdx, entity.getStr("sale_main_name"));

            reportId = entity.get("appoint_report_id");
            if (reportId != null) {
                Entity report = Db.use(DB_CENTER).queryOne("select cr.sale_main_name ,cr.report_date ,st.sale_team_name ,cr.report_status ,cr.deal_date ,co.signing_date ,co.house_name ,co.order_total_price  from customer_report cr \n" +
                        "join sale_team st on st.sale_team_id = cr.sale_team_id \n" +
                        "left join customer_order_rel cor on cor.report_id = cr.report_id and cor.deleted = 0\n" +
                        "left join customer_order co on co.order_id = cor.ticket_number and co.order_type = '10'\n" +
                        "where cr.report_id = ? and (co.order_state in ('10','20') or co.order_state is null)", entity.get("appoint_report_id"));
                if (report != null) {
                    writer.writeCellValue(colIdx++, rowIdx, report.getStr("sale_team_name"))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("report_status").equals("10")? "否":"是")
                            .writeCellValue(colIdx++, rowIdx, report.getStr("deal_date"))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("sale_main_name"))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("report_status").equals("80")? "是":"否")
                            .writeCellValue(colIdx++, rowIdx, report.getStr("signing_date"))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("order_total_price"))
                            .writeCellValue(colIdx++, rowIdx, report.getStr("house_name"));
                }
            }
            writer.flush();
        }
        writer.close();
    }
}
